-- @owner: @qumin20145
-- @date: 2022-06-13
-- @testpoint: 子查询使用distinct->子查询的目标列使用序列

--step1:建表并插入数据; expect:成功
drop table if exists t_ustore_query_0012_1;
drop table if exists t_ustore_query_0012_2;
create  table t_ustore_query_0012_1
(id int ,
c_num number,
c_varchar varchar(1024),
c_blob blob,
c_date date
) with (storage_type=ustore);

begin
	for i in 1..100 loop
		insert into t_ustore_query_0012_1 values(i,i*i,lpad('123abc',i,'abc'),lpad('11100121',i,'1100')::blob,to_timestamp(to_char('1800-01-01 10:51:47'),'yyyy-mm-dd hh24:mi:ss') );
	end loop;
end;
/
create table t_ustore_query_0012_2 with (storage_type=ustore) as select id,c_num,c_varchar from t_ustore_query_0012_1 where id <30;

--step2:建索引; expect:成功
drop index if exists i_ustore_query_0012_1;
drop index if exists i_ustore_query_0012_2;
drop index if exists i_ustore_query_0012_3;
drop index if exists i_ustore_query_0012_4;
create unique index i_ustore_query_0012_1 on t_ustore_query_0012_1(id);
create unique index i_ustore_query_0012_2 on t_ustore_query_0012_1(id,c_varchar);
create unique index i_ustore_query_0012_3 on t_ustore_query_0012_1(id,c_varchar,c_date);
create unique index i_ustore_query_0012_4 on t_ustore_query_0012_2(id,c_varchar);

--step3:建序列; expect:成功
drop sequence if exists sqe_ustore_query_0012;
create sequence sqe_ustore_query_0012 start with 10 maxvalue 1000 increment by 1 cycle;

--step4:子查询使用distinct->子查询的目标列使用序列; expect:成功
explain plan for select distinct t0.id new_id from (select distinct sqe_ustore_query_0012.nextval new_id,id from t_ustore_query_0012_1 
where id <20 ) t0 inner join t_ustore_query_0012_2 t on t.id=t0.id order by t0.id;
explain plan for select distinct t0.id new_id from (select  sqe_ustore_query_0012.nextval new_id,id from t_ustore_query_0012_1 
where id <20 ) t0 inner join t_ustore_query_0012_2 t on t.id=t0.id order by t0.id;
select distinct t0.id new_id from (select distinct sqe_ustore_query_0012.nextval new_id,id from t_ustore_query_0012_1 
where id <20 ) t0  inner join t_ustore_query_0012_2 t on t.id=t0.id order by t0.id;
select distinct t0.id new_id from (select sqe_ustore_query_0012.nextval new_id,id from t_ustore_query_0012_1 
where id <20 ) t0 inner join t_ustore_query_0012_2 t on t.id=t0.id order by t0.id;

--step5:清理环境; expect:成功
drop table t_ustore_query_0012_1;
drop table t_ustore_query_0012_2;
drop sequence sqe_ustore_query_0012;